/*
 * MIT License
 *
 * Copyright (c) 2022 zycra
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

package com.gitee.zycra.jdbc.common;

import com.gitee.zycra.jdbc.enums.SQLConditionEnum;
import com.gitee.zycra.jdbc.enums.SQLLinkEnum;
import com.gitee.zycra.jdbc.model.PageQueryWrapper;
import com.gitee.zycra.jdbc.model.PageResult;
import com.gitee.zycra.jdbc.util.SQLBlock;
import com.gitee.zycra.jdbc.util.SQLChain;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import java.lang.reflect.ParameterizedType;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Common SQL statements in most cases.
 *
 * <p>The business Dao needs to inherit the current class.
 * <p>A {@link JdbcTemplate} spring bean should be defined in application context.
 * <p>When need to define other datasource, call the {@link BaseDao#setJdbcTemplate(JdbcTemplate)} and autowried new jdbcTemplate bean.
 *
 * @param <T> data object type.
 * @author zycra
 * @since 1.0.0
 */
@Repository
public class BaseDao<T> {

    private static final String SELECT = "SELECT ";
    private static final String COUNT = "COUNT(*)";
    private static final String FROM = " FROM ";
    private static final String WHERE = " WHERE ";
    private static final String LIMIT1 = " LIMIT 1";

    /**
     * Native global print SQL log switch, default is false.
     *
     * @since 1.0.0
     */
    @Value("${spring.datasource.print-sql:false}")
    private boolean printSQL;

    /**
     * JdbcTemplate bean.
     *
     * @since 1.0.0
     */
    private JdbcTemplate jdbcTemplate;

    /**
     * CommonSelector bean.
     *
     * @since 1.0.0
     */
    @Autowired
    private CommonSelector commonSelector;

    /**
     * Method for chagne the current datasource.
     *
     * @param jdbcTemplate jdbcTemplate bean to change.
     * @since 1.0.0
     */
    @Autowired
    protected void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * Return current jdbcTemplate bean for execute custom SQL.
     *
     * @return current jdbcTemplate bean.
     * @since 1.0.0
     */
    protected JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    /**
     * Single table insert.
     *
     * @param model data object instance to insert.
     * @return affected rows.
     * @since 1.0.0
     */
    protected final int insertIntoTable(T model) {
        return insertIntoTable(model, printSQL);
    }

    /**
     * Single table insert.
     *
     * @param model    data object instance to insert.
     * @param printSQL whether print SQL log.
     * @return affected rows.
     * @since 1.0.0
     */
    protected final int insertIntoTable(T model, boolean printSQL) {
        checkModelNotNull(model);
        List<Object> paramList = new ArrayList<>();
        String sql = getInsertSql(model, paramList);
        int result = jdbcTemplate.update(sql, paramList.toArray());
        commonSelector.printDebugSqlLog("insertIntoTable", sql, paramList, result, printSQL);
        return result;
    }

    /**
     * Single table batch insert.
     *
     * <p>When the database column is defined as not null, the corresponding field value must be not null.
     *
     * @param list data object instance list to insert.
     * @return affected rows.
     * @since 1.0.0
     */
    protected final int batchInsertIntoTable(List<T> list) {
        return batchInsertIntoTable(list, printSQL);
    }

    /**
     * Single table batch insert.
     *
     * <p>When the database column is defined as not null, the corresponding field value must be not null.
     *
     * @param list     data object instance list to insert.
     * @param printSQL whether print SQL log.
     * @return affected rows.
     * @since 1.0.0
     */
    protected final int batchInsertIntoTable(List<T> list, boolean printSQL) {
        Assert.notEmpty(list, "list is empty");
        List<Object> paramList = new ArrayList<>();
        String sql = getBatchInsertSql(list, paramList);
        int result = jdbcTemplate.update(sql, paramList.toArray());
        commonSelector.printDebugSqlLog("batchInsertIntoTable", sql, paramList, result, printSQL);
        return result;
    }

    /**
     * Update not null field by primary key field.
     *
     * @param model data object instance.
     * @return affected rows.
     * @since 1.0.0
     */
    protected final int updateTableById(T model) {
        return updateTableById(model, printSQL);
    }

    /**
     * Update not null field by primary key field.
     *
     * @param model    data object instance.
     * @param printSQL whether print SQL log.
     * @return affected rows.
     * @since 1.0.0
     */
    protected final int updateTableById(T model, boolean printSQL) {
        checkModelNotNull(model);
        List<Object> paramList = new ArrayList<>();
        String sql = getUpdateByIdSql(model, paramList);
        int result = jdbcTemplate.update(sql, paramList.toArray());
        commonSelector.printDebugSqlLog("updateTableById", sql, paramList, result, printSQL);
        return result;
    }

    /**
     * Delete record by primary key field.
     *
     * @param model data object instance.
     * @return affected rows.
     * @since 1.0.0
     */
    protected final int deleteTableById(T model) {
        return deleteTableById(model, printSQL);
    }

    /**
     * Delete record by primary key field.
     *
     * @param model    data object instance.
     * @param printSQL whether print SQL log.
     * @return affected rows.
     * @since 1.0.0
     */
    protected final int deleteTableById(T model, boolean printSQL) {
        checkModelNotNull(model);
        Class<T> clazz = getTClass();
        String className = clazz.getName();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, className);
        String idColumnName = DataObjectContainer.getIdColumnName(clazz);
        checkIdColumnDefined(idColumnName, className);
        Object idColumnValue = DataObjectContainer.getIdColumnValue(model);
        checkIdColumnValueNotNull(idColumnValue, className);
        String sql = "DELETE" + FROM + table + WHERE + idColumnName + " = ?";
        int result = jdbcTemplate.update(sql, idColumnValue);
        commonSelector.printDebugSqlLog("deleteTableById", sql, List.of(idColumnValue), result, printSQL);
        return result;
    }

    /**
     * Select one record by primary key field.
     *
     * @param model data object instance.
     * @return query result.
     * @since 1.0.0
     */
    protected final T selectTableById(T model) {
        return selectTableById(model, printSQL);
    }

    /**
     * Select one record by primary key field.
     *
     * @param model    data object instance.
     * @param printSQL whether print SQL log.
     * @return query result.
     * @since 1.0.0
     */
    protected final T selectTableById(T model, boolean printSQL) {
        checkModelNotNull(model);
        Class<T> clazz = getTClass();
        String className = clazz.getName();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, className);
        String idColumnName = DataObjectContainer.getIdColumnName(clazz);
        checkIdColumnDefined(idColumnName, className);
        Object idColumnValue = DataObjectContainer.getIdColumnValue(model);
        checkIdColumnValueNotNull(idColumnValue, className);

        String sql = SELECT + getAllColumnSqlForSelect() + FROM + table + WHERE + idColumnName + " = ?";
        return commonSelector.selectOneByParam(jdbcTemplate, sql, clazz, List.of(idColumnValue), printSQL);
    }

    /**
     * Count rows by not null field equals.
     *
     * @param model data object instance.
     * @return count result.
     * @since 1.0.0
     */
    protected final Integer countTableByNotNullColumnEquals(T model) {
        return countTableByNotNullColumnEquals(model, printSQL);
    }

    /**
     * Count rows by not null field equals.
     *
     * @param model    data object instance.
     * @param printSQL whether print SQL log.
     * @return count result.
     * @since 1.0.0
     */
    protected final Integer countTableByNotNullColumnEquals(T model, boolean printSQL) {
        checkModelNotNull(model);
        Class<T> clazz = getTClass();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, clazz.getName());
        Map<String, Object> notNullColumnMap = DataObjectContainer.getNotNullColumnMap(model);

        String sql = SELECT + COUNT + FROM + table;
        SQLChain sqlChain = SQLChain.builder();
        for (Map.Entry<String, Object> entry : notNullColumnMap.entrySet()) {
            sqlChain.addBlock(SQLBlock.of(SQLLinkEnum.AND, entry.getKey(), SQLConditionEnum.EQUALS, entry.getValue()));
        }
        sqlChain.build();
        Integer result = commonSelector.selectOneByParam(jdbcTemplate, sql + sqlChain.getSQL(), Integer.class, sqlChain.getParamList(), printSQL);
        return result == null ? Integer.valueOf(0) : result;
    }

    /**
     * Select one record by not null field equals.
     *
     * @param model data object instance.
     * @return query result.
     * @since 1.0.0
     */
    protected final T selectOneTableByNotNullColumnEquals(T model) {
        return selectOneTableByNotNullColumnEquals(model, printSQL);
    }

    /**
     * Select one record by not null field equals.
     *
     * @param model    data object instance.
     * @param printSQL whether print SQL log.
     * @return query result.
     * @since 1.0.0
     */
    protected final T selectOneTableByNotNullColumnEquals(T model, boolean printSQL) {
        checkModelNotNull(model);
        Class<T> clazz = getTClass();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, clazz.getName());
        Map<String, Object> notNullColumnMap = DataObjectContainer.getNotNullColumnMap(model);

        String sql = SELECT + getAllColumnSqlForSelect() + FROM + table;
        SQLChain sqlChain = SQLChain.builder();
        for (Map.Entry<String, Object> entry : notNullColumnMap.entrySet()) {
            sqlChain.addBlock(SQLBlock.of(SQLLinkEnum.AND, entry.getKey(), SQLConditionEnum.EQUALS, entry.getValue()));
        }
        sqlChain.build();
        return commonSelector.selectOneByParam(jdbcTemplate, sql + sqlChain.getSQL() + LIMIT1, clazz, sqlChain.getParamList(), printSQL);
    }

    /**
     * Select multi records by not null field equals.
     *
     * @param model data object instance.
     * @return query result.
     * @since 1.0.0
     */
    protected final List<T> selectTableByNotNullColumnEquals(T model) {
        return selectTableByNotNullColumnEquals(model, printSQL);
    }

    /**
     * Select multi records by not null field equals.
     *
     * @param model    data object instance.
     * @param printSQL whether print SQL log.
     * @return query result.
     * @since 1.0.0
     */
    protected final List<T> selectTableByNotNullColumnEquals(T model, boolean printSQL) {
        checkModelNotNull(model);
        Class<T> clazz = getTClass();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, clazz.getName());
        Map<String, Object> notNullColumnMap = DataObjectContainer.getNotNullColumnMap(model);

        String sql = SELECT + getAllColumnSqlForSelect() + FROM + table;
        SQLChain sqlChain = SQLChain.builder();
        for (Map.Entry<String, Object> entry : notNullColumnMap.entrySet()) {
            sqlChain.addBlock(SQLBlock.of(SQLLinkEnum.AND, entry.getKey(), SQLConditionEnum.EQUALS, entry.getValue()));
        }
        sqlChain.build();
        return commonSelector.selectByParam(jdbcTemplate, sql + sqlChain.getSQL(), clazz, sqlChain.getParamList(), printSQL);
    }

    /**
     * Page query records by param.
     *
     * @param param query param.
     * @return query result.
     * @since 1.0.0
     */
    protected final PageResult<T> selectTableForPage(PageQueryWrapper param) {
        return selectTableForPage(param, printSQL);
    }

    /**
     * Page query records by param.
     *
     * @param param    query param.
     * @param printSQL whether print SQL log.
     * @return query result.
     * @since 1.0.0
     */
    protected final PageResult<T> selectTableForPage(PageQueryWrapper param, boolean printSQL) {
        return commonSelector.selectForPage(jdbcTemplate, getTClass(), param, printSQL);
    }

    /**
     * Return all columns SQL piece with current data object type.
     *
     * @return all columns SQL piece.
     * @since 1.0.0
     */
    protected final String getAllColumnSqlForSelect() {
        Class<T> clazz = getTClass();
        Set<String> allColumnSet = DataObjectContainer.getAllColumnSet(clazz);
        checkAllColumnNotNull(allColumnSet, clazz.getName());
        StringBuilder sqlBuilder = new StringBuilder();
        boolean first = true;
        for (String key : allColumnSet) {
            if (!first) {
                sqlBuilder.append(", ");
            }
            sqlBuilder.append(key);
            first = false;
        }
        return sqlBuilder.toString();
    }

    /**
     * Return count SQL piece with current data object type.
     *
     * @return SQL piece.
     * @since 1.0.0
     */
    protected final String getCountSqlWithOutParam() {
        Class<T> clazz = getTClass();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, clazz.getName());
        return SELECT + COUNT + FROM + table;
    }

    /**
     * Return select all column SQL piece with current data object type.
     *
     * @return SQL piece.
     * @since 1.0.0
     */
    protected final String getSelectAllColumnSql() {
        Class<T> clazz = getTClass();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, clazz.getName());
        return SELECT + getAllColumnSqlForSelect() + FROM + table;
    }

    /**
     * Return common insert SQL.
     *
     * @param model     data object instance.
     * @param paramList all insert param list pointer for resolve.
     * @return common insert SQL.
     * @since 1.0.0
     */
    private String getInsertSql(T model, List<Object> paramList) {
        Class<T> clazz = getTClass();
        String className = clazz.getName();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, className);
        Map<String, Object> notNullColumnMap = DataObjectContainer.getNotNullColumnMap(model);
        checkAllColumnNotNull(notNullColumnMap, className);

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("INSERT INTO ").append(table).append("(");
        StringBuilder valueBuilder = new StringBuilder();
        boolean first = true;
        for (Map.Entry<String, Object> entry : notNullColumnMap.entrySet()) {
            if (!first) {
                sqlBuilder.append(", ");
                valueBuilder.append(", ");
            }
            sqlBuilder.append(entry.getKey());
            paramList.add(entry.getValue());
            valueBuilder.append("?");
            first = false;
        }
        return sqlBuilder.append(") VALUES (").append(valueBuilder).append(")").toString();
    }

    /**
     * Return batch insert SQL.
     *
     * @param list      data object instance list.
     * @param paramList all insert param list pointer for resolve.
     * @return batch insert SQL.
     * @since 1.0.0
     */
    private String getBatchInsertSql(List<T> list, List<Object> paramList) {
        Class<T> clazz = getTClass();
        String className = clazz.getName();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, className);
        Set<String> allColumnSet = DataObjectContainer.getBatchInsertColumnSet(clazz);
        checkAllColumnNotNull(allColumnSet, className);

        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
        sqlBuilder.append(table).append("(");
        StringBuilder valueBuilder = new StringBuilder("(");
        boolean first = true;
        List<String> columnList = new ArrayList<>();
        for (String columnName : allColumnSet) {
            if (!first) {
                sqlBuilder.append(", ");
                valueBuilder.append(", ");
            }
            sqlBuilder.append(columnName);
            valueBuilder.append("?");
            columnList.add(columnName);
            first = false;
        }
        sqlBuilder.append(") VALUES ");
        valueBuilder.append(")");
        StringBuilder paramBuilder = new StringBuilder();
        first = true;
        for (T t : list) {
            if (!first) {
                paramBuilder.append(", ");
            }
            paramBuilder.append(valueBuilder);
            Map<String, Object> columnMap = DataObjectContainer.getAllColumnMap(t);
            for (String column : columnList) {
                paramList.add(columnMap.get(column));
            }
            first = false;
        }
        return sqlBuilder.append(paramBuilder).toString();
    }

    /**
     * Return update not null fields by primary key SQL.
     *
     * @param model     data object instance.
     * @param paramList all update param list pointer for resolve.
     * @return update not null fields by primary key SQL.
     * @since 1.0.0
     */
    private String getUpdateByIdSql(T model, List<Object> paramList) {
        Class<T> clazz = getTClass();
        String className = clazz.getName();
        String table = DataObjectContainer.getTable(clazz);
        checkTableDefine(table, className);
        Map<String, Object> notNullColumnMap = DataObjectContainer.getNotNullColumnMap(model);
        checkAllColumnNotNull(notNullColumnMap, className);
        String idColumnName = DataObjectContainer.getIdColumnName(clazz);
        checkIdColumnDefined(idColumnName, className);
        Object idColumnValue = DataObjectContainer.getIdColumnValue(model);
        checkIdColumnValueNotNull(idColumnValue, className);
        Assert.isTrue(notNullColumnMap.size() > 1, "only id column is not null");

        StringBuilder sqlBuilder = new StringBuilder("UPDATE ");
        sqlBuilder.append(table).append(" SET ");
        boolean first = true;
        for (Map.Entry<String, Object> entry : notNullColumnMap.entrySet()) {
            if (idColumnName.equals(entry.getKey())) {
                continue;
            }
            if (!first) {
                sqlBuilder.append(", ");
            }
            sqlBuilder.append(entry.getKey()).append(" = ?");
            paramList.add(entry.getValue());
            first = false;
        }
        sqlBuilder.append(WHERE).append(idColumnName).append(" = ?");
        paramList.add(idColumnValue);
        return sqlBuilder.toString();
    }

    private void checkModelNotNull(T model) {
        Assert.notNull(model, "model is null");
    }

    private void checkTableDefine(String table, String className) {
        Assert.notNull(table, "no table define for model " + className);
    }

    private void checkIdColumnDefined(String idColumnName, String className) {
        Assert.notNull(idColumnName, "no id column defined " + className);
    }

    private void checkIdColumnValueNotNull(Object idColumnValue, String className) {
        Assert.notNull(idColumnValue, "id column value is null " + className);
    }

    private void checkAllColumnNotNull(Map<String, Object> allColumnMap, String className) {
        Assert.notEmpty(allColumnMap, "all column is null " + className);
    }

    private void checkAllColumnNotNull(Set<String> allColumnSet, String className) {
        Assert.notEmpty(allColumnSet, "all column is null " + className);
    }

    @SuppressWarnings("unchecked")
    private Class<T> getTClass() {
        return (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
    }
}
